﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class ZoneDA : IZoneDA
    {
        private string AISLE_END = "@AISLE_END";
        private string AISLE_START = "@AISLE_START";
        private string CLEAN_LOCATION = "@CLEAN_LOCATION";
        private string CREATE_ASSIGNMENTS = "@CREATE_ASSIGNMENTS";
        private string CREATED_BY = "@CREATED_BY";
        private string DESCR = "@DESCR";
        private string IN_LOC = "@IN_LOC";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string LABOR_MAX_CASECNT = "@LABOR_MAX_CASECNT";
        private string LABOR_MAX_CUBE = "@LABOR_MAX_CUBE";
        private string LABOR_MAX_WEIGHT = "@LABOR_MAX_WEIGHT";
        private string MAX_CASECNT = "@MAX_CASECNT";
        private string MAX_CUBE = "@MAX_CUBE";
        private string MAX_PALLETS_PER_SKU = "@MAX_PALLETS_PER_SKU";
        private string MAX_PICK_LINES = "@MAX_PICK_LINES";
        private string MAX_PICKING_CONTAINERS = "@MAX_PICKING_CONTAINERS";
        private string MAX_WEIGHT = "@MAX_WEIGHT";
        private string OUT_LOC = "@OUT_LOC";
        private string PICK_TO_LOC = "@PICK_TO_LOC";
        private string REPLENISHMENT_FLAG = "@REPLENISHMENT_FLAG";
        private string REPLENISHMENT_HOT_LEVEL = "@REPLENISHMENT_HOT_LEVEL";
        private string REPLENISHMENT_METHOD = "@REPLENISHMENT_METHOD";
        private string SLOT_END = "@SLOT_END";
        private string SLOT_START = "@SLOT_START";
        private string SQL_CHECK_ZONE_ID_UNIQUE = " SELECT COUNT(1) FROM @_@ZONE WHERE ZONE_ID = @ZONE_ID ";
        private string SQL_DELETE_ZONE = " DELETE FROM @_@ZONE WHERE ZONE_ID = @ZONE_ID AND SYSTEM_CREATED = 'N' ";
        private string SQL_INSERT_ZONE = " INSERT INTO @_@ZONE ( WH_ID, ZONE_ID, DESCR, VERIFICATION_METHOD, PICK_TO_LOC, IN_LOC, OUT_LOC, UOM1_PICK_METHOD, UOM2_PICK_METHOD, UOM3_PICK_METHOD, UOM4_PICK_METHOD, UOM5_PICK_METHOD, UOM6_PICK_METHOD, CLEAN_LOCATION, TOP_OFF, REPLENISHMENT_FLAG, REPLENISHMENT_HOT_LEVEL, REPLENISHMENT_METHOD, MAX_CUBE, MAX_WEIGHT, MAX_CASECNT, MAX_PALLETS_PER_SKU, CREATE_ASSIGNMENTS, ZONE_BREAK, MAX_PICK_LINES, MAX_PICKING_CONTAINERS, LABOR_MAX_CASECNT, LABOR_MAX_CUBE, LABOR_MAX_WEIGHT, AISLE_START, AISLE_END, SLOT_START, SLOT_END, IS_ACTIVE, SYSTEM_CREATED, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @ZONE_ID, @DESCR, @VERIFICATION_METHOD, @PICK_TO_LOC, @IN_LOC, @OUT_LOC, @UOM1_PICK_METHOD, @UOM2_PICK_METHOD, @UOM3_PICK_METHOD, @UOM4_PICK_METHOD, @UOM5_PICK_METHOD, @UOM6_PICK_METHOD, @CLEAN_LOCATION, @TOP_OFF, @REPLENISHMENT_FLAG, @REPLENISHMENT_HOT_LEVEL, @REPLENISHMENT_METHOD, @MAX_CUBE, @MAX_WEIGHT, @MAX_CASECNT, @MAX_PALLETS_PER_SKU, @CREATE_ASSIGNMENTS, @ZONE_BREAK, @MAX_PICK_LINES, @MAX_PICKING_CONTAINERS, @LABOR_MAX_CASECNT, @LABOR_MAX_CUBE, @LABOR_MAX_WEIGHT, @AISLE_START, @AISLE_END, @SLOT_START, @SLOT_END, @IS_ACTIVE, @SYSTEM_CREATED, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_ZONE = " SELECT  ROWID, WH_ID, ZONE_ID, DESCR, VERIFICATION_METHOD, PICK_TO_LOC, IN_LOC, OUT_LOC, UOM1_PICK_METHOD, UOM2_PICK_METHOD, UOM3_PICK_METHOD, UOM4_PICK_METHOD, UOM5_PICK_METHOD, UOM6_PICK_METHOD, CLEAN_LOCATION, TOP_OFF, REPLENISHMENT_FLAG, REPLENISHMENT_HOT_LEVEL, REPLENISHMENT_METHOD, MAX_CUBE, MAX_WEIGHT, MAX_CASECNT, MAX_PALLETS_PER_SKU, CREATE_ASSIGNMENTS, ZONE_BREAK, MAX_PICK_LINES, MAX_PICKING_CONTAINERS, LABOR_MAX_CASECNT, LABOR_MAX_CUBE, LABOR_MAX_WEIGHT, AISLE_START, AISLE_END, SLOT_START, SLOT_END, IS_ACTIVE, SYSTEM_CREATED, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@ZONE ";
        private string SQL_UPDATE_ZONE = " UPDATE @_@ZONE SET WH_ID = @WH_ID, DESCR = @DESCR, VERIFICATION_METHOD = @VERIFICATION_METHOD, PICK_TO_LOC = @PICK_TO_LOC, IN_LOC = @IN_LOC, OUT_LOC = @OUT_LOC, UOM1_PICK_METHOD = @UOM1_PICK_METHOD, UOM2_PICK_METHOD = @UOM2_PICK_METHOD, UOM3_PICK_METHOD = @UOM3_PICK_METHOD, UOM4_PICK_METHOD = @UOM4_PICK_METHOD, UOM5_PICK_METHOD = @UOM5_PICK_METHOD, UOM6_PICK_METHOD = @UOM6_PICK_METHOD, CLEAN_LOCATION = @CLEAN_LOCATION, TOP_OFF = @TOP_OFF, REPLENISHMENT_FLAG = @REPLENISHMENT_FLAG, REPLENISHMENT_HOT_LEVEL = @REPLENISHMENT_HOT_LEVEL, REPLENISHMENT_METHOD = @REPLENISHMENT_METHOD, MAX_CUBE = @MAX_CUBE, MAX_WEIGHT = @MAX_WEIGHT, MAX_CASECNT = @MAX_CASECNT, MAX_PALLETS_PER_SKU = @MAX_PALLETS_PER_SKU, CREATE_ASSIGNMENTS = @CREATE_ASSIGNMENTS, ZONE_BREAK = @ZONE_BREAK, MAX_PICK_LINES = @MAX_PICK_LINES, MAX_PICKING_CONTAINERS = @MAX_PICKING_CONTAINERS, LABOR_MAX_CASECNT = @LABOR_MAX_CASECNT, LABOR_MAX_CUBE = @LABOR_MAX_CUBE, LABOR_MAX_WEIGHT = @LABOR_MAX_WEIGHT, AISLE_START = @AISLE_START, AISLE_END = @AISLE_END, SLOT_START = @SLOT_START, SLOT_END = @SLOT_END, IS_ACTIVE = @IS_ACTIVE, SYSTEM_CREATED = @SYSTEM_CREATED, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ZONE_ID = @ZONE_ID  ";
        private string SYSTEM_CREATED = "@SYSTEM_CREATED";
        private string TOP_OFF = "@TOP_OFF";
        private string TS = "@TS";
        private string UOM1_PICK_METHOD = "@UOM1_PICK_METHOD";
        private string UOM2_PICK_METHOD = "@UOM2_PICK_METHOD";
        private string UOM3_PICK_METHOD = "@UOM3_PICK_METHOD";
        private string UOM4_PICK_METHOD = "@UOM4_PICK_METHOD";
        private string UOM5_PICK_METHOD = "@UOM5_PICK_METHOD";
        private string UOM6_PICK_METHOD = "@UOM6_PICK_METHOD";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VERIFICATION_METHOD = "@VERIFICATION_METHOD";
        private string WH_ID = "@WH_ID";
        private string ZONE_BREAK = "@ZONE_BREAK";
        private string ZONE_ID = "@ZONE_ID";

        public bool CheckZoneIDUnique(string zoneID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ZONE_ID", zoneID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_ZONE_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckZoneIDUnique(DataBase dataBase, DbTransaction tran, string zoneID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ZONE_ID", zoneID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_ZONE_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteZone(List<string> zoneID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < zoneID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ZONE_ID", zoneID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ZONE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteZone(DataBase dataBase, DbTransaction tran, List<string> zoneID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < zoneID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ZONE_ID", zoneID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ZONE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllZone(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ZONE.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllZone(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ZONE.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public ZoneInfo GetZoneByID(string zoneID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ZONE.Replace("@_@", whLoginID) + " WHERE ZONE_ID = @ZONE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ZONE_ID", zoneID) };
            ZoneInfo zoneInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    zoneInfo = new ZoneInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ZONE_ID"].ToString(), reader["DESCR"].ToString(), reader["VERIFICATION_METHOD"].ToString(), reader["PICK_TO_LOC"].ToString(), reader["IN_LOC"].ToString(), reader["OUT_LOC"].ToString(), reader["UOM1_PICK_METHOD"].ToString(), reader["UOM2_PICK_METHOD"].ToString(), reader["UOM3_PICK_METHOD"].ToString(), reader["UOM4_PICK_METHOD"].ToString(), reader["UOM5_PICK_METHOD"].ToString(), reader["UOM6_PICK_METHOD"].ToString(), reader["CLEAN_LOCATION"].ToString(), reader["TOP_OFF"].ToString(), reader["REPLENISHMENT_FLAG"].ToString(), reader["REPLENISHMENT_HOT_LEVEL"].ToString(), reader["REPLENISHMENT_METHOD"].ToString(), float.Parse((reader["MAX_CUBE"].ToString() == "") ? "0" : reader["MAX_CUBE"].ToString()), float.Parse((reader["MAX_WEIGHT"].ToString() == "") ? "0" : reader["MAX_WEIGHT"].ToString()), (reader["MAX_CASECNT"].ToString() != "") ? decimal.Parse(reader["MAX_CASECNT"].ToString()) : 0M, (reader["MAX_PALLETS_PER_SKU"].ToString() != "") ? int.Parse(reader["MAX_PALLETS_PER_SKU"].ToString()) : 0, reader["CREATE_ASSIGNMENTS"].ToString(), reader["ZONE_BREAK"].ToString(), (reader["MAX_PICK_LINES"].ToString() != "") ? int.Parse(reader["MAX_PICK_LINES"].ToString()) : 0, (reader["MAX_PICKING_CONTAINERS"].ToString() != "") ? int.Parse(reader["MAX_PICKING_CONTAINERS"].ToString()) : 0, (reader["LABOR_MAX_CASECNT"].ToString() != "") ? int.Parse(reader["LABOR_MAX_CASECNT"].ToString()) : 0, (reader["LABOR_MAX_CUBE"].ToString() != "") ? int.Parse(reader["LABOR_MAX_CUBE"].ToString()) : 0, (reader["LABOR_MAX_WEIGHT"].ToString() != "") ? int.Parse(reader["LABOR_MAX_WEIGHT"].ToString()) : 0, (reader["AISLE_START"].ToString() != "") ? int.Parse(reader["AISLE_START"].ToString()) : 0, (reader["AISLE_END"].ToString() != "") ? int.Parse(reader["AISLE_END"].ToString()) : 0, (reader["SLOT_START"].ToString() != "") ? int.Parse(reader["SLOT_START"].ToString()) : 0, (reader["SLOT_END"].ToString() != "") ? int.Parse(reader["SLOT_END"].ToString()) : 0, reader["IS_ACTIVE"].ToString(), reader["SYSTEM_CREATED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return zoneInfo;
        }

        public ZoneInfo GetZoneByID(DataBase dataBase, DbTransaction tran, string zoneID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ZONE.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ZONE_ID", zoneID) };
            ZoneInfo zoneInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                zoneInfo = new ZoneInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ZONE_ID"].ToString(), reader["DESCR"].ToString(), reader["VERIFICATION_METHOD"].ToString(), reader["PICK_TO_LOC"].ToString(), reader["IN_LOC"].ToString(), reader["OUT_LOC"].ToString(), reader["UOM1_PICK_METHOD"].ToString(), reader["UOM2_PICK_METHOD"].ToString(), reader["UOM3_PICK_METHOD"].ToString(), reader["UOM4_PICK_METHOD"].ToString(), reader["UOM5_PICK_METHOD"].ToString(), reader["UOM6_PICK_METHOD"].ToString(), reader["CLEAN_LOCATION"].ToString(), reader["TOP_OFF"].ToString(), reader["REPLENISHMENT_FLAG"].ToString(), reader["REPLENISHMENT_HOT_LEVEL"].ToString(), reader["REPLENISHMENT_METHOD"].ToString(), float.Parse((reader["MAX_CUBE"].ToString() == "") ? "0" : reader["MAX_CUBE"].ToString()), float.Parse((reader["MAX_WEIGHT"].ToString() == "") ? "0" : reader["MAX_WEIGHT"].ToString()), (reader["MAX_CASECNT"].ToString() != "") ? decimal.Parse(reader["MAX_CASECNT"].ToString()) : 0M, (reader["MAX_PALLETS_PER_SKU"].ToString() != "") ? int.Parse(reader["MAX_PALLETS_PER_SKU"].ToString()) : 0, reader["CREATE_ASSIGNMENTS"].ToString(), reader["ZONE_BREAK"].ToString(), (reader["MAX_PICK_LINES"].ToString() != "") ? int.Parse(reader["MAX_PICK_LINES"].ToString()) : 0, (reader["MAX_PICKING_CONTAINERS"].ToString() != "") ? int.Parse(reader["MAX_PICKING_CONTAINERS"].ToString()) : 0, (reader["LABOR_MAX_CASECNT"].ToString() != "") ? int.Parse(reader["LABOR_MAX_CASECNT"].ToString()) : 0, (reader["LABOR_MAX_CUBE"].ToString() != "") ? int.Parse(reader["LABOR_MAX_CUBE"].ToString()) : 0, (reader["LABOR_MAX_WEIGHT"].ToString() != "") ? int.Parse(reader["LABOR_MAX_WEIGHT"].ToString()) : 0, (reader["AISLE_START"].ToString() != "") ? int.Parse(reader["AISLE_START"].ToString()) : 0, (reader["AISLE_END"].ToString() != "") ? int.Parse(reader["AISLE_END"].ToString()) : 0, (reader["SLOT_START"].ToString() != "") ? int.Parse(reader["SLOT_START"].ToString()) : 0, (reader["SLOT_END"].ToString() != "") ? int.Parse(reader["SLOT_END"].ToString()) : 0, reader["IS_ACTIVE"].ToString(), reader["SYSTEM_CREATED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return zoneInfo;
        }

        public DataSet GetZoneByQueryList(List<string> sqlWhere, ZoneQueryEntity zoneQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_ZONE.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!zoneQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, zoneQuery.CurrentPage, zoneQuery.PageSize, zoneQuery.SortField, zoneQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetZoneByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, ZoneQueryEntity zoneQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_ZONE.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!zoneQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, zoneQuery.CurrentPage, zoneQuery.PageSize, zoneQuery.SortField, zoneQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertZone(ZoneInfo zoneInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    zoneInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Zone_Parameters(zoneInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ZONE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertZone(DataBase dataBase, DbTransaction tran, ZoneInfo zoneInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    zoneInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Zone_Parameters(zoneInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ZONE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Zone_Parameters(ZoneInfo zoneInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.ZONE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DESCR, SqlDbType.VarChar, 100), new SqlParameter(this.VERIFICATION_METHOD, SqlDbType.VarChar, 10), new SqlParameter(this.PICK_TO_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.IN_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.OUT_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.UOM1_PICK_METHOD, SqlDbType.VarChar, 1), new SqlParameter(this.UOM2_PICK_METHOD, SqlDbType.VarChar, 1), new SqlParameter(this.UOM3_PICK_METHOD, SqlDbType.VarChar, 1), new SqlParameter(this.UOM4_PICK_METHOD, SqlDbType.VarChar, 1), new SqlParameter(this.UOM5_PICK_METHOD, SqlDbType.VarChar, 1), new SqlParameter(this.UOM6_PICK_METHOD, SqlDbType.VarChar, 1), new SqlParameter(this.CLEAN_LOCATION, SqlDbType.VarChar, 1), new SqlParameter(this.TOP_OFF, SqlDbType.VarChar, 1), new SqlParameter(this.REPLENISHMENT_FLAG, SqlDbType.VarChar, 1), 
                new SqlParameter(this.REPLENISHMENT_HOT_LEVEL, SqlDbType.VarChar, 1), new SqlParameter(this.REPLENISHMENT_METHOD, SqlDbType.VarChar, 10), new SqlParameter(this.MAX_CUBE, SqlDbType.Float, 8), new SqlParameter(this.MAX_WEIGHT, SqlDbType.Float, 8), new SqlParameter(this.MAX_CASECNT, SqlDbType.Decimal, 13), new SqlParameter(this.MAX_PALLETS_PER_SKU, SqlDbType.Int, 4), new SqlParameter(this.CREATE_ASSIGNMENTS, SqlDbType.VarChar, 1), new SqlParameter(this.ZONE_BREAK, SqlDbType.VarChar, 1), new SqlParameter(this.MAX_PICK_LINES, SqlDbType.Int, 4), new SqlParameter(this.MAX_PICKING_CONTAINERS, SqlDbType.Int, 4), new SqlParameter(this.LABOR_MAX_CASECNT, SqlDbType.Int, 4), new SqlParameter(this.LABOR_MAX_CUBE, SqlDbType.Int, 4), new SqlParameter(this.LABOR_MAX_WEIGHT, SqlDbType.Int, 4), new SqlParameter(this.AISLE_START, SqlDbType.Int, 4), new SqlParameter(this.AISLE_END, SqlDbType.Int, 4), new SqlParameter(this.SLOT_START, SqlDbType.Int, 4), 
                new SqlParameter(this.SLOT_END, SqlDbType.Int, 4), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.SYSTEM_CREATED, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(zoneInfo.WhID))
            {
                paramArray[0].Value = zoneInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = zoneInfo.ZoneID;
            paramArray[2].Value = zoneInfo.Descr;
            paramArray[3].Value = zoneInfo.VerificationMethod;
            if (!string.IsNullOrEmpty(zoneInfo.PickToLoc))
            {
                paramArray[4].Value = zoneInfo.PickToLoc;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            paramArray[5].Value = zoneInfo.InLoc;
            paramArray[6].Value = zoneInfo.OutLoc;
            paramArray[7].Value = zoneInfo.Uom1PickMethod;
            paramArray[8].Value = zoneInfo.Uom2PickMethod;
            paramArray[9].Value = zoneInfo.Uom3PickMethod;
            paramArray[10].Value = zoneInfo.Uom4PickMethod;
            paramArray[11].Value = zoneInfo.Uom5PickMethod;
            paramArray[12].Value = zoneInfo.Uom6PickMethod;
            if (!string.IsNullOrEmpty(zoneInfo.CleanLocation))
            {
                paramArray[13].Value = zoneInfo.CleanLocation;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(zoneInfo.TopOff))
            {
                paramArray[14].Value = zoneInfo.TopOff;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(zoneInfo.ReplenishmentFlag))
            {
                paramArray[15].Value = zoneInfo.ReplenishmentFlag;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(zoneInfo.ReplenishmentHotLevel))
            {
                paramArray[0x10].Value = zoneInfo.ReplenishmentHotLevel;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            paramArray[0x11].Value = zoneInfo.ReplenishmentMethod;
            paramArray[0x12].Value = zoneInfo.MaxCube;
            paramArray[0x13].Value = zoneInfo.MaxWeight;
            paramArray[20].Value = zoneInfo.MaxCasecnt;
            paramArray[0x15].Value = zoneInfo.MaxPalletsPerSku;
            paramArray[0x16].Value = zoneInfo.CreateAssignments;
            paramArray[0x17].Value = zoneInfo.ZoneBreak;
            paramArray[0x18].Value = zoneInfo.MaxPickLines;
            paramArray[0x19].Value = zoneInfo.MaxPickingContainers;
            paramArray[0x1a].Value = zoneInfo.LaborMaxCasecnt;
            paramArray[0x1b].Value = zoneInfo.LaborMaxCube;
            paramArray[0x1c].Value = zoneInfo.LaborMaxWeight;
            paramArray[0x1d].Value = zoneInfo.AisleStart;
            paramArray[30].Value = zoneInfo.AisleEnd;
            paramArray[0x1f].Value = zoneInfo.SlotStart;
            paramArray[0x20].Value = zoneInfo.SlotEnd;
            paramArray[0x21].Value = zoneInfo.IsActive;
            paramArray[0x22].Value = zoneInfo.SystemCreated;
            paramArray[0x23].Value = zoneInfo.CreatedBy;
            paramArray[0x24].Value = zoneInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateZone(ZoneInfo zoneInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    zoneInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Zone_Parameters(zoneInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ZONE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateZone(DataBase dataBase, DbTransaction tran, ZoneInfo zoneInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    zoneInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Zone_Parameters(zoneInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ZONE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

